﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.Common;


    public class DataProvider
    {

        public static string strConnection = ConfigurationManager.ConnectionStrings["SimpleHotel_ConStr"].ConnectionString;


        public static DataTable GetProductByManufacturer(int mid,int index, int pagesize, out int totalRows)
        {
            totalRows = 0;
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(strConnection);
            SqlCommand Cmd = new SqlCommand("wf_GetProductByManufacturer", conn);
            Cmd.CommandType = CommandType.StoredProcedure;
            Cmd.Parameters.Add("@Mid", SqlDbType.Int).Value = mid;
            Cmd.Parameters.Add("@PageNum", SqlDbType.Int).Value = index;
            Cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pagesize;
            Cmd.Parameters.Add("@totalRows", SqlDbType.Int).Direction = ParameterDirection.Output;

            conn.Open();
            IDataReader dr = Cmd.ExecuteReader();
            dt.Load(dr);
            dr.Close();
            conn.Close();

            totalRows = Convert.ToInt32(Cmd.Parameters["@totalRows"].Value);
            return dt;
        }


        public static DataTable GetProductPopular(int index, int pagesize, out int totalRows)
        {
            totalRows = 0;
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(strConnection);
            SqlCommand Cmd = new SqlCommand("wf_GetProductPopular", conn);
            Cmd.CommandType = CommandType.StoredProcedure;
            Cmd.Parameters.Add("@PageNum", SqlDbType.Int).Value = index;
            Cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pagesize;
            Cmd.Parameters.Add("@totalRows", SqlDbType.Int).Direction = ParameterDirection.Output;

            conn.Open();
            IDataReader dr = Cmd.ExecuteReader();
            dt.Load(dr);
            dr.Close();
            conn.Close();

            totalRows = Convert.ToInt32(Cmd.Parameters["@totalRows"].Value);
            return dt;
        }

        public static DataTable GetProductNew(int index, int pagesize, out int totalRows)
        {
            totalRows = 0;
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(strConnection);
            SqlCommand Cmd = new SqlCommand("wf_GetProductNew", conn);
            Cmd.CommandType = CommandType.StoredProcedure;
            Cmd.Parameters.Add("@PageNum", SqlDbType.Int).Value = index;
            Cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pagesize;
            Cmd.Parameters.Add("@totalRows", SqlDbType.Int).Direction = ParameterDirection.Output;

            conn.Open();
            IDataReader dr = Cmd.ExecuteReader();
            dt.Load(dr);
            dr.Close();
            conn.Close();

            totalRows = Convert.ToInt32(Cmd.Parameters["@totalRows"].Value);
            return dt;
        }



        public static DataTable GetData(int id, int index, int pagesize, out int totalRows)
        {
            totalRows = 0;
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(strConnection);
            SqlCommand Cmd = new SqlCommand("wf_GetProductByCatID", conn);
            Cmd.CommandType = CommandType.StoredProcedure;
            Cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            Cmd.Parameters.Add("@PageNum", SqlDbType.Int).Value = index;
            Cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pagesize;
            Cmd.Parameters.Add("@totalRows", SqlDbType.Int).Direction = ParameterDirection.Output;

            conn.Open();
            IDataReader dr = Cmd.ExecuteReader();
            dt.Load(dr);
            dr.Close();
            conn.Close();

            totalRows = Convert.ToInt32(Cmd.Parameters["@totalRows"].Value);       
            return dt;
        }


        public static DataTable GetDataProductSearch(string keyWord, int index, int pagesize, out int totalRows)
        {
            totalRows = 0;
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(strConnection);
            SqlCommand Cmd = new SqlCommand("wf_GetProductBySearch", conn);
            Cmd.CommandType = CommandType.StoredProcedure;
            Cmd.Parameters.Add("@keyWord", SqlDbType.NVarChar).Value = keyWord;
            Cmd.Parameters.Add("@PageNum", SqlDbType.Int).Value = index;
            Cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pagesize;
            Cmd.Parameters.Add("@totalRows", SqlDbType.Int).Direction = ParameterDirection.Output;

            conn.Open();
            IDataReader dr = Cmd.ExecuteReader();
            dt.Load(dr);
            dr.Close();
            conn.Close();

            string strTotalRows = Cmd.Parameters["@totalRows"].Value.ToString();
            if (strTotalRows == "")
            {
                totalRows = 0;
            }
            else if (strTotalRows == null)
            {
                totalRows = 0;
            }
            else
            {
                totalRows = Convert.ToInt32(Cmd.Parameters["@totalRows"].Value);
            }
            return dt;
        }

        public static DataSet ExecuteDataSet(string strSPName,params object[] paras)
        {
            DataSet ds = null;
            try
            {
                SqlDatabase db = new SqlDatabase(strConnection);
                ds = db.ExecuteDataSet(strSPName, paras);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
 
            }
            
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="CommadType"></param>
        /// <param name="CommandText"></param>
        /// <param name="paras"></param>
        public static DataSet ExecuteDataSet(string spName, List<SPParam> parameters)
        {
            DataSet ds = null;
            try
            {
                SqlDatabase db = new SqlDatabase(strConnection);
                var command = db.FillParameters(spName, parameters);
                ds = db.ExecuteDataSet(command);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            } 
            finally
            {

            }
            
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="CommadType"></param>
        /// <param name="CommandText"></param>
        /// <param name="paras"></param>
        public static DataSet ExecuteDataSet(CommandType CommadType, string CommandText, params object[] paras)
        {
            DataSet ds = null;
            try
            {
                SqlDatabase db = new SqlDatabase(strConnection);
                
                ds = db.ExecuteDataSet(CommadType, CommandText);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {

            }

        }
        
        public static IDataReader ExecuteReader(string strSPName, params object[] paras)
        {
            IDataReader dr = null;
            try
            {
                SqlDatabase db = new SqlDatabase(strConnection);
                dr = db.ExecuteReader(strSPName, paras);
                return dr;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
 
            }
        }

        /// <summary>
        /// execute a SP with list of parameters (
        /// Note: must put the parameter in order
        /// </summary>
        /// <param name="strSPName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string strSPName, params object[] paras)
        {
            int result = -1;
            try
            {
                SqlDatabase db = new SqlDatabase(strConnection);
                result = db.ExecuteNonQuery(strSPName, paras);
                return result;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {

            }
            
        }
        /// <summary>
        /// Execute a SP with list of parameters
        /// </summary>
        /// <param name="strSPName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static void ExecuteNonQuery(string strSPName, List<SPParam> parameters)
        {
            int result = -1;
            try
            {
                SqlDatabase db = new SqlDatabase(strConnection);
                var command = db.FillParameters(strSPName, parameters);
                result = db.ExecuteNonQuery(command);
                
                //update output parameters
                for (int i = 0; i < parameters.Count; i++) {
                    if (parameters[i].IsOutput) {
                        parameters[i].Value = command.Parameters[parameters[i].Name].Value;
                    }
                }

            }
            catch (Exception ex)
            {
            }
        }
        /// <summary>
        /// Execute a SQL query text 
        /// </summary>
        /// <param name="SQLText"></param>
        /// <returns></returns>
        public static string ExecuteNonQuery(string SQLText)
        {
            int result = -1;
            try
            {
                SqlDatabase db = new SqlDatabase(strConnection);
                //var command = db.FillParameters(strSPName, parameters);
                result = db.ExecuteNonQuery(CommandType.Text, SQLText);

                return string.Empty;
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSPName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string strSPName, params object[] paras)
        {
            object obj = null;
            try
            {
                SqlDatabase db = new SqlDatabase(strConnection);
                obj = db.ExecuteScalar(strSPName, paras);
                return obj;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
 
            }
            
        }        
    }

public class SPParam
{
    private bool _isOutput = false;
    public bool IsOutput 
    {
        get { return this._isOutput; } 
        set { this._isOutput = value; }
    }

    private SqlDbType _sqlType = SqlDbType.Int;
    public SqlDbType SqlType { 
        get { return this._sqlType; }
        set { this._sqlType = value; }
    }

    private object _value;
    public object Value {
        get { return this._value;}
        set { this._value = value; }
    }

    private string _name;
    public string Name
    {
        get { return this._name; }
        set { this._name = value; }
    }

    private int _size = 4;
    public int Size {
        get { return this._size; }
        set { this._size = value; }
    }

    public SPParam ()
	{

	}


}

public static class DataExtensions
{
    public static DbCommand FillParameters(this SqlDatabase db, string spName, List<SPParam> parameters)
    {
        var command = db.GetStoredProcCommand(spName);
        parameters.ForEach( p => {
            if (!p.IsOutput)
                db.AddInParameter(command, p.Name, p.SqlType, p.Value);
            else
                db.AddOutParameter(command, p.Name, p.SqlType, p.Size);
        });

        return command;
    }
}